using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 车辆统计查询工具
/// 覆盖场景：V-010 车辆统计（按日期分组统计进出数量）
/// </summary>
public class GetVehicleStatsTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetVehicleStatsTool> _logger;

    public string Name => "get_vehicle_stats";
    public string Description => "统计车辆进出数量，支持按日期分组。用于回答'统计本周车辆进出'、'每天进校车辆统计'等问题。";

    public GetVehicleStatsTool(
        IDatabaseExecutor executor,
        ILogger<GetVehicleStatsTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            int days = parameters.ContainsKey("days") ? Convert.ToInt32(parameters["days"]) : 7;
            string? startDate = parameters.ContainsKey("startDate") ? parameters["startDate"]?.ToString() : null;

            if (string.IsNullOrEmpty(startDate))
            {
                startDate = DateTime.Today.AddDays(-days).ToString("yyyy-MM-dd");
            }

            var sql = BuildQuery(startDate);

            _logger.LogInformation("Executing vehicle stats query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "车辆统计查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql,
                    ["days"] = days
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing vehicle stats query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    private string BuildQuery(string startDate)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    DATE(InTime) AS date,");
        sb.AppendLine("    COUNT(*) AS entry_count,");
        sb.AppendLine("    SUM(CASE WHEN OutTime IS NOT NULL THEN 1 ELSE 0 END) AS exit_count,");
        sb.AppendLine("    COUNT(*) - SUM(CASE WHEN OutTime IS NOT NULL THEN 1 ELSE 0 END) AS still_on_campus");
        sb.AppendLine("FROM TblMotorVehicleManagement");
        sb.AppendLine($"WHERE InTime >= '{startDate}'");
        sb.AppendLine("GROUP BY DATE(InTime)");
        sb.AppendLine("ORDER BY date DESC");
        sb.AppendLine("LIMIT 30");

        return sb.ToString();
    }
}
